package com.qmrz.nb.common.domain;

import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelSheet implements Serializable {
    /** Sheet名称 */
    private String sheetName;
    /** Sheet 序列 */
    private int sheetIndex;
    /** 标题行 */
    private int titleRow;
    /** 数据行 */
    private int beginDataIndex;
    /** 标题 */
    private List<String> titles = new ArrayList<>();
    /** 数据 */
    private List<List<String>> data = new ArrayList<>();

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public int getSheetIndex() {
        return sheetIndex;
    }

    public void setSheetIndex(int sheetIndex) {
        this.sheetIndex = sheetIndex;
    }

    public int getTitleRow() {
        return titleRow;
    }

    public void setTitleRow(int titleRow) {
        this.titleRow = titleRow;
    }

    public int getbeginDataIndex() {
        return beginDataIndex;
    }

    public void setbeginDataIndex(int beginDataIndex) {
        this.beginDataIndex = beginDataIndex;
    }

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }

    public List<List<String>> getData() {
        return data;
    }

    public void setData(List<List<String>> data) {
        this.data = data;
    }

    /**
    * 读取标题
     *
    * @author July july_sky@foxmail.com
    * @date 2022/08/14 16:28:42
    */
    public List<String> readTitle(Excel excel) {
        this.setTitles(new ArrayList<>());
        Sheet sheet = excel.getWorkbook().getSheetAt(this.getSheetIndex());
        int rows = sheet.getLastRowNum();
        if (rows < this.getTitleRow()) {
            return new ArrayList<>();
        }
        Row row = sheet.getRow(this.getTitleRow());
        for (int colIndex = row.getFirstCellNum(); colIndex < row.getLastCellNum(); colIndex ++) {
            getTitles().add(this.readCellValue(row.getCell(colIndex)));
        }
        return getTitles();
    }

    /**
    * 读取数据
     *
    * @author July july_sky@foxmail.com
    * @date 2022/08/14 16:29:06
    */
    public List<List<String>> readData(Excel excel) {
        this.setData(new ArrayList<>());
        int a = this.getSheetIndex();
        Sheet sheet = excel.getWorkbook().getSheetAt(this.getSheetIndex());
        int rows = sheet.getLastRowNum();
        if (rows < this.getbeginDataIndex()) {
            return new ArrayList<>();
        }
        List<String> rowData;
        Row row;
        for (int rowIndex = this.getbeginDataIndex(); rowIndex < rows; rowIndex ++) {
            rowData = new ArrayList<>();
            row = sheet.getRow(rowIndex);
            if (row == null) {
                break;
            }
            for (int colIndex = row.getFirstCellNum(); colIndex < row.getLastCellNum(); colIndex ++) {
                rowData.add(this.readCellValue(row.getCell(colIndex)));
            }
            getData().add(rowData);
        }
        return getData();
    }

    public String readCellValue(Cell cell) {
        switch (cell.getCellType()) {
            // 数字
            case NUMERIC:
                //如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                }else {
                    String value= String.valueOf(cell.getNumericCellValue());
                    String[] split = value.split("\\.");
                    //整型不保留小数部分
                    if (split[1].length()==1 && split[1].equals("0")){
                        value = split[0];
                    }
                   return value.trim();
                }
                // 字符串
            case STRING:
                return cell.getStringCellValue().trim();
            // Boolean
            case BOOLEAN:
                return cell.getBooleanCellValue()+"";
            // 公式
            case FORMULA:
                String cellValue = "";
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    try{
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException exception) {
                        cellValue = "";
                    }
                }
                return cellValue;
            // 空值
            case BLANK:
                return "";
            // 故障
            case ERROR:
                return null;
            default:
                return null;
        }
    }

    public static void main(String[] args) throws IOException {
        Excel excel = new Excel("6月 运一附件1：运营设施设备月度统计数据.xlsx",new FileInputStream(new File("F:\\projects\\霏霏\\运一，上海地铁生成docx一期简单的需求文档\\6月 运一附件1：运营设施设备月度统计数据.xlsx")));
        ExcelSheet excelSheet = new ExcelSheet();
        excelSheet.setSheetIndex(2);
        excelSheet.setTitleRow(2);
        excelSheet.setbeginDataIndex(3);
        System.out.println(excelSheet.readData(excel));
    }
}
